/*
 =============================================================================
 FILE...............: BookingDAO.java
 COMMENTS...........: Class implemented by using the DAO (Data Access Object)
 					  design pattern for manipulating Booking objects while
 					  accessing the database. This class extends the GenericDAO
 					  abstract class.
 =============================================================================
 AUTHOR(S)..........: Everton Ranielly de Sousa Cavalcante
 					  Department of Informatics and Applied Mathematics
 					  Federal University of Rio Grande do Norte
 LAST MODIFICATION..: September 17th, 2012
 ============================================================================= 					  
*/


/* ========================================================================= */
/* PACKAGES			                                                         */
/* ------------------------------------------------------------------------- */
package dao;


/* ========================================================================= */
/* IMPORTS			                                                         */
/* ------------------------------------------------------------------------- */
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Booking;
import model.Client;
import model.Flight;


/* ========================================================================= */
/* CLASS BookingDAO                                                          */
/* ------------------------------------------------------------------------- */
/**
 * Class implemented by using the DAO (Data Access Object) design pattern for 
 * manipulating <code>Booking</code> objects while accessing the database. 
 * This class extends the <code>GenericDAO</code> abstract class.
 * @author Everton Cavalcante
 */
public class BookingDAO extends GenericDAO<Booking, String>
{
	/** Default constructor */
	public BookingDAO()
	{ /* Empty */ }
	
	
	/**
	 * Inserts a booking
	 * @param booking Booking to insert
	 */
	public void insert(Booking booking)
	{		
		try 
		{
			// Opens a connection 
			openConnection();
			
			FlightDAO flightDAO = new FlightDAO();		// FlightDAO instance
			ClientDAO clientDAO = new ClientDAO();		// ClientDAO instance
			
			// Departure flight index by the flight identification
			int departureFlightIndex = flightDAO.getFlightIndex(booking.getFlight01().getIdentification());
			
			// Return flight index by the flight identification
			int returnFlightIndex = flightDAO.getFlightIndex(booking.getFlight02().getIdentification());
			
			// Client index by the client name
			int clientIndex = clientDAO.getClientIndex(booking.getClient().getName());
			
			// SQL query to be executed
			String query = "INSERT INTO bookings VALUES(?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(query);
			
			// Data to be inserted
			pstmt.setString(1, booking.getBookingCode());
			pstmt.setInt(2, departureFlightIndex);
			pstmt.setInt(3, returnFlightIndex);			
			pstmt.setInt(4, clientIndex);
			pstmt.setDouble(5, booking.getValue());
			pstmt.setString(6, booking.getObservations());
			
			// Executes query
			pstmt.execute();
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when inserting booking:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
	}


	/**
	 * Updates a booking
	 * @param booking Booking to update
	 * @param code Booking code
	 */
	public void update(Booking booking, String code)
	{
		try 
		{
			// Opens a connection 
			openConnection();
			
			FlightDAO flightDAO = new FlightDAO();		// FlightDAO instance
			ClientDAO clientDAO = new ClientDAO();		// ClientDAO instance
			
			// Departure flight index by the flight identification
			int departureFlightIndex = flightDAO.getFlightIndex(booking.getFlight01().getIdentification());
						
			// Return flight index by the flight identification
			int returnFlightIndex = flightDAO.getFlightIndex(booking.getFlight02().getIdentification());
						
			// Client index by the client name
			int clientIndex = clientDAO.getClientIndex(booking.getClient().getName());
			
			// SQL query to be executed
			String query = "UPDATE bookings SET departureflight = ?, returnflight = ?, client = ?, value = ?, observations = ? " +
				"WHERE bookingcode = ?";
			pstmt = conn.prepareStatement(query);
			
			// Data to be updated
			pstmt.setInt(1, departureFlightIndex);
			pstmt.setInt(2, returnFlightIndex);
			pstmt.setInt(3, clientIndex);
			pstmt.setDouble(4, booking.getValue());
			pstmt.setString(5, booking.getObservations());
			pstmt.setString(6, code);
				
			// Executes query
			pstmt.execute();
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when updating booking:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
	}
	
	
	/**
	 * Deletes a booking
	 * @param code Booking code
	 */
	public void delete(String code)
	{
		try 
		{
			// Opens a connection 
			openConnection();
			
			// SQL query to be executed
			String query = "DELETE FROM bookings WHERE bookingcode = ?";
			pstmt = conn.prepareStatement(query);
				
			// Data to be deleted
			pstmt.setString(1, code);
				
			// Executes query
			pstmt.execute();
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when deleting booking:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
	}
	
	
	/**
	 * Returns the specified booking
	 * @param code Booking code
	 * @return Specified booking
	 */
	public Booking get(String code)
	{
		try 
		{
			// Opens a connection 
			openConnection();
				
			// SQL query to be executed
			String query = "SELECT * FROM bookings WHERE bookingcode = '" + code + "'";
			stmt = conn.prepareStatement(query);
				
			// Booking to be returned
			Booking result = null;
				
			// Executes query
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next())
			{
				result = new Booking();
				result.setBookingCode(rs.getString("bookingcode"));
				
				FlightDAO flightDAO = new FlightDAO();		// FlightDAO instance
				ClientDAO clientDAO = new ClientDAO();		// ClientDAO instance
				
				Flight departureFlight = flightDAO.get(rs.getInt("departureflight"));
				result.setFlight01(departureFlight);
				
				Flight returnFlight = flightDAO.get(rs.getInt("returnflight"));
				result.setFlight02(returnFlight);
				
				Client client = clientDAO.get(rs.getInt("client"));
				result.setClient(client);
				
				result.setValue(rs.getDouble("value"));
				result.setObservations(rs.getString("observations"));
			}
				
			// Returns the specified booking
			if (result == null)
			{
				System.out.println("Error:\nBooking not found");
				System.exit(1);
			}
			
			return result;
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when searching booking:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
				
		return null;
	}
	
	
	/**
	 * Returns all registered bookings
	 * @return Registered bookings
	 */
	public List<Booking> getAll()
	{
		try 
		{
			// Opens a connection 
			openConnection();
			
			// SQL query to be executed
			String query = "SELECT * FROM bookings";
			
			// List of bookings
			List<Booking> bookings = new ArrayList<Booking>();
			
			// Executes query
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next())
			{
				// Current booking
				Booking booking = new Booking();
				booking.setBookingCode(rs.getString("bookingcode"));
				
				FlightDAO flightDAO = new FlightDAO();		// FlightDAO instance
				ClientDAO clientDAO = new ClientDAO();		// ClientDAO instance
				
				Flight departureFlight = flightDAO.get(rs.getInt("departureflight"));
				booking.setFlight01(departureFlight);
				
				Flight returnFlight = flightDAO.get(rs.getInt("returnflight"));
				booking.setFlight02(returnFlight);
				
				Client client = clientDAO.get(rs.getInt("client"));
				booking.setClient(client);
				
				booking.setValue(rs.getDouble("value"));
				booking.setObservations(rs.getString("observations"));
				
				// Adds the current booking
				bookings.add(booking);
			}
			
			// Returns registered bookings
			if (bookings.isEmpty())
			{
				System.out.println("Error:\nNo bookings registered");
				System.exit(1);
			}
			
			return bookings;
		}
		catch (SQLException e) 
		{ 
			System.out.println("Error when searching bookings:\n" + e.getMessage());
			System.exit(1);
		}
		catch (IOException e)
		{
			e.printStackTrace();
			System.exit(1);
		}
		finally
		{ closeConnection(); }
		
		return null;
	}
}
